package com.jic.veteran.service.impl;

import cn.hutool.core.date.DateUtil;
import com.jic.veteran.component.VeteranSecurityContext;
import com.jic.veteran.dao.AlumniAddresslistAdminMapper;
import com.jic.veteran.dto.AlumniSchoolAddresslist;
import com.jic.veteran.service.AlumniAddresslistService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 学校信息Service实现类
 * Created by mars on 2021/11/24.
 */
@Slf4j
@Service
public class AlumniAddresslistServiceImpl implements AlumniAddresslistService {

    @Autowired
    private AlumniAddresslistAdminMapper alumniAddresslistAdminMapper;

//    public List<AlumniSchoolAddresslist> findAll(){
//        return repository.findAll();
//    }


    public int importExcel(MultipartFile excelFile){
        if (excelFile==null|| excelFile.getSize()==0){
            log.error("文件上传错误，重新上传");
        }
        String filename = excelFile.getOriginalFilename();
        if (!(filename.endsWith(".xls")|| filename.endsWith(".xlsx"))){
            log.error("文件上传格式错误，请重新上传");
        }

        try {
            if (filename.endsWith(".xls")){
                loadXLS(excelFile);
            }else {
                loadXLSX(excelFile);
            }
        }catch (IOException e) {
            e.printStackTrace();
            log.error("文件内容读取失败，请重试");
        }
        return 1;
    }


    public int loadXLS(MultipartFile file) throws IOException {
        List<AlumniSchoolAddresslist> list =new ArrayList<>();

        InputStream inputStream = file.getInputStream();
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

        //读取第一张sheet
        HSSFSheet sheet = workbook.getSheetAt(0);
        String errorMsg="";
        //遍历每一行Excel获取内容
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            HSSFRow row = sheet.getRow(rowNum);
            if (row!=null){
                AlumniSchoolAddresslist alumniSchoolAddresslist = new AlumniSchoolAddresslist();
                //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                //然后poi会根据字符串读取它

//                System.out.println("用户名"+VeteranSecurityContext.getUserName());
                if(row.getCell(0) != null){
                    row.getCell(0).setCellType(CellType.STRING);
                    alumniSchoolAddresslist.setId(Long.parseLong(row.getCell(0).getStringCellValue()));
                }
                if(row.getCell(22) != null){
                    row.getCell(22).setCellType(CellType.STRING);
                    alumniSchoolAddresslist.setStatus(Integer.parseInt(row.getCell(22).getStringCellValue()));

                }


                System.out.println(row.getCell(2).getStringCellValue());
                alumniSchoolAddresslist.setName(row.getCell(1).getStringCellValue());
                alumniSchoolAddresslist.setSchoolName(row.getCell(2).getStringCellValue());
                alumniSchoolAddresslist.setSex(row.getCell(3).getStringCellValue());
                alumniSchoolAddresslist.setAge(row.getCell(4).getStringCellValue());
                alumniSchoolAddresslist.setYearsWorking(row.getCell(5).getStringCellValue());
                alumniSchoolAddresslist.setContent(row.getCell(6).getStringCellValue());
                alumniSchoolAddresslist.setBusiness(row.getCell(7).getStringCellValue());
                alumniSchoolAddresslist.setCity(row.getCell(8).getStringCellValue());
                alumniSchoolAddresslist.setStudentGrade(row.getCell(9).getStringCellValue());
                alumniSchoolAddresslist.setStudentClass(row.getCell(10).getStringCellValue());
                alumniSchoolAddresslist.setUnit(row.getCell(11).getStringCellValue());
                alumniSchoolAddresslist.setTitle(row.getCell(12).getStringCellValue());
                alumniSchoolAddresslist.setJobDescription(row.getCell(13).getStringCellValue());
                alumniSchoolAddresslist.setIcon(row.getCell(14).getStringCellValue());
                alumniSchoolAddresslist.setPhone(row.getCell(15).getStringCellValue());
                alumniSchoolAddresslist.setEmail(row.getCell(16).getStringCellValue());
                alumniSchoolAddresslist.setEducationBackground(row.getCell(17).getStringCellValue());
                alumniSchoolAddresslist.setCreateTime(DateUtil.date());
//                alumniSchoolAddresslist.setCreateUser(VeteranSecurityContext.getUserName());
                alumniSchoolAddresslist.setUpdateTime(DateUtil.date());
//                alumniSchoolAddresslist.setUpdateUser(VeteranSecurityContext.getUserName());
                System.out.println(alumniSchoolAddresslist);
                alumniAddresslistAdminMapper.insertOrUpdate(alumniSchoolAddresslist);

            }
        }
        return 1;
    }


    public int loadXLSX(MultipartFile file) throws IOException {


        InputStream inputStream = file.getInputStream();
        XSSFWorkbook Workbook = new XSSFWorkbook(inputStream);

        XSSFSheet sheet = Workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        System.out.println(lastRowNum);


        List<AlumniSchoolAddresslist> list;
        for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);
            System.out.println(row.getCell(2));
            if (row!=null){
                AlumniSchoolAddresslist alumniSchoolAddresslist = new AlumniSchoolAddresslist();
                //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                //然后poi会根据字符串读取它

//                System.out.println("用户名"+VeteranSecurityContext.getUserName());
                if(row.getCell(0) != null){
                    row.getCell(0).setCellType(CellType.STRING);
                    alumniSchoolAddresslist.setId(Long.parseLong(row.getCell(0).getStringCellValue()));
                }
                if(row.getCell(22) != null){
                    row.getCell(22).setCellType(CellType.STRING);
                    alumniSchoolAddresslist.setStatus(Integer.parseInt(row.getCell(22).getStringCellValue()));

                }



                alumniSchoolAddresslist.setName(row.getCell(1).getStringCellValue());
                alumniSchoolAddresslist.setSchoolName(row.getCell(2).getStringCellValue());
                alumniSchoolAddresslist.setSex(row.getCell(3).getStringCellValue());
                alumniSchoolAddresslist.setAge(row.getCell(4).getStringCellValue());
                alumniSchoolAddresslist.setYearsWorking(row.getCell(5).getStringCellValue());
                alumniSchoolAddresslist.setContent(row.getCell(6).getStringCellValue());
                alumniSchoolAddresslist.setBusiness(row.getCell(7).getStringCellValue());
                alumniSchoolAddresslist.setCity(row.getCell(8).getStringCellValue());
                alumniSchoolAddresslist.setStudentGrade(row.getCell(9).getStringCellValue());
                alumniSchoolAddresslist.setStudentClass(row.getCell(10).getStringCellValue());
                alumniSchoolAddresslist.setUnit(row.getCell(11).getStringCellValue());
                alumniSchoolAddresslist.setTitle(row.getCell(12).getStringCellValue());
                alumniSchoolAddresslist.setJobDescription(row.getCell(13).getStringCellValue());
                alumniSchoolAddresslist.setIcon(row.getCell(14).getStringCellValue());
                alumniSchoolAddresslist.setPhone(row.getCell(15).getStringCellValue());
                alumniSchoolAddresslist.setEmail(row.getCell(16).getStringCellValue());
                alumniSchoolAddresslist.setEducationBackground(row.getCell(17).getStringCellValue());
                alumniSchoolAddresslist.setCreateTime(DateUtil.date());
//                alumniSchoolAddresslist.setCreateUser(VeteranSecurityContext.getUserName());
                alumniSchoolAddresslist.setUpdateTime(DateUtil.date());
//                alumniSchoolAddresslist.setUpdateUser(VeteranSecurityContext.getUserName());
                System.out.println(alumniSchoolAddresslist);
                alumniAddresslistAdminMapper.insertOrUpdate(alumniSchoolAddresslist);

            }
        }
        return 1;
    }

}
